import openpyxl
import pymysql

workbook = openpyxl.load_workbook('user.xlsx')
worksheet = workbook['sheet1']

n = 1
for i in range(1, 2001):
    for j in range(1, 11):
        if j == 1:
            worksheet.cell(row=i, column=j, value=n)
        elif j == 2:
            worksheet.cell(row=i, column=j, value='411481200306066666')
        elif j == 3:
            worksheet.cell(row=i, column=j, value='蔡徐坤')
        elif j == 4:
            worksheet.cell(row=i, column=j, value='123456')
        elif j == 5:
            worksheet.cell(row=i, column=j, value='10086')
        elif j == 6:
            worksheet.cell(row=i, column=j, value='123456@gmail.com')
        elif j == 7:
            worksheet.cell(row=i, column=j, value=18)
        elif j == 8:
            worksheet.cell(row=i, column=j, value='男')
        elif j == 9:
            worksheet.cell(row=i, column=j, value='上海')
        else:
            worksheet.cell(row=i, column=j, value='测试工程师')
    n += 1

workbook.save('user.xlsx')

class PyMySQL:
    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset

    def insert(self, data=None):
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(f'insert into user_info (id, idcard, username, pwd, telphone, email, age, sex, address, job) values {data}')
                    # 'insert into user_info (id, idcard, username, pwd, telphone, email, age, sex, address, job) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',data)
                db.commit()
        except Exception as e:
            print('添加失败', e)
        finally:
            db.close()


if __name__ == '__main__':
    n = 1
    add = PyMySQL('localhost', 'root', '122311', 'test')
    for i in worksheet.iter_rows():
        data = []
        for j in range(0, 10):
            data.append(i[j].value)
        print(f'第{n}次添加成功')
        n += 1
        add.insert(tuple(data))